#include "sqlcontroller.h"
#include <QDebug>
#include <QCryptographicHash>
#include <QSqlQuery>
#include <QSqlError>

SqlController *SqlController::s_instance = nullptr;
SqlController::SqlController()
{

}

SqlController * SqlController::getInstance()
{
    if (s_instance == nullptr)
    {
        s_instance = new SqlController();
    }
    return s_instance;
}

QString SqlController::strToMd5(QString str)
{
    QString strMd5;
    QByteArray qba;
    QString befStr = BEFORE_CODE + str + AFTER_CODE;
    qba = QCryptographicHash::hash(befStr.toLatin1(), QCryptographicHash::Md5);
    qba = QCryptographicHash::hash(qba, QCryptographicHash::Md5);
    strMd5.append(qba.toHex());
    return strMd5;
}

bool SqlController::initDataBase()
{
    qDebug() << "DB drivers : " << QSqlDatabase::drivers();
    m_db = QSqlDatabase::addDatabase(DB_DRIVER);
    m_db.setHostName(DB_HOST_NAME);
    m_db.setPort(DB_PORT);
    m_db.setUserName(DB_USER_NAME);
    m_db.setPassword(DB_PASSWORD);
    m_db.setDatabaseName(DB_DATABASE_NAME);
    if (m_db.open())
    {
        qDebug() << "connect success";
        return true;
    }
    qDebug() << "connect failed";
    return false;
}

bool SqlController::goRegister(QString name, QString pwd, int auth)
{
    QString mdPassword = strToMd5(pwd); //加密密码
    QString strReg = QString("INSERT INTO user(name, password, auth) VALUES('%1', '%2', %3)").arg(name).arg(mdPassword).arg(auth);
    qDebug() << "strReg" << strReg;
    QSqlQuery regQuery;
    if(regQuery.exec(strReg))
    {
        return true;
    }
    else
    {
        qDebug() << "reg failed! err:" << regQuery.lastError().text();
        return false;
    }
}

bool SqlController::goLogin(QString name, QString pwd)
{
    QString mdPassword = strToMd5(pwd); //加密密码
    QString strLogin = "SELECT * FROM user WHERE name=:name";
    QSqlQuery loginQuery;
    loginQuery.prepare(strLogin);
    loginQuery.bindValue(":name", name);
    if (loginQuery.exec())
    {
        while (loginQuery.next())
        {
            //
            QString strPwd = loginQuery.value("password").toString();
            if(strPwd == mdPassword)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
    return false;
}

UserInfo SqlController::getUserInfo(QString name)
{
    UserInfo uInfo;
    QString strSql = "SELECT * FROM user WHERE name=?";
    QSqlQuery query;
    query.prepare(strSql);
    query.addBindValue(name);
    if(query.exec())
    {
        while(query.next())
        {
            uInfo.id = query.value("id").toInt();
            uInfo.name = query.value("name").toString();
            uInfo.password = query.value("password").toString();
            uInfo.auth = query.value("auth").toInt();
            break;
        }
    }
    return uInfo;
}

bool SqlController::removeUser(QString name)
{
    QString strSql = "DELETE FROM user WHERE name = ?";
    QSqlQuery query;
    query.prepare(strSql);
    query.bindValue(0, name);
    if(query.exec())
    {
        return true;
    }
    return false;
}

QList<UserInfo> SqlController::getAllUserInfo()
{
    QList<UserInfo> uList;
    QString strSql = "SELECT * FROM user";
    QSqlQuery query;
    if (query.exec(strSql))
    {
        while (query.next())
        {
            UserInfo uInfo;
            uInfo.id = query.value("id").toInt();
            uInfo.name = query.value("name").toString();
            uInfo.password = query.value("password").toString();
            uInfo.auth = query.value("auth").toInt();
            uList.append(uInfo);
        }
    }
    return uList;
}

QSqlQueryModel *SqlController::getStudentModel()
{
    QSqlQueryModel *model = new QSqlQueryModel();
    model->setQuery("SELECT * FROM student");
    model->setHeaderData(0, Qt::Horizontal, "ID");
    model->setHeaderData(1, Qt::Horizontal, "姓名");
    model->setHeaderData(2, Qt::Horizontal, "性别");
    model->setHeaderData(3, Qt::Horizontal, "年龄");
    model->setHeaderData(4, Qt::Horizontal, "地址");
    return model;
}
